package com.academic_system.mapper;

import com.academic_system.model.MarkCondition;

/**
 * @author 20155884 王晓阳
 * @className : WarningSqlProvider
 * @Description 动态查询学业警示
 **/
public class WarningSqlProvider {
    /**
     * @Description 动态从Warning表查找学业警示
     * @author 20155884 王晓阳
     * @methodName selectWarning
     * @param markCondition
     * @returnType String
     **/
    public String selectWarning(MarkCondition markCondition){
        StringBuffer sql = new StringBuffer("select warning.*,term.*,student.stu_info_id,student.stu_num,student.stu_name " +
                "from warning,term,student " +
                "where warning.term_id=term.term_id " +
                "AND warning.stu_info_id=student.stu_info_id ");
        if (markCondition.getStuInfoId()!=null){
            sql.append(" and warning.stu_info_id=#{stuInfoId}");
            System.out.println("学号:"+markCondition.getStuInfoId());
        }
        if (markCondition.getTermYear() != null&&!("".equals(markCondition.getTermYear()))) {
            sql.append(" and term_year=#{termYear}");
            System.out.println("SqlProvider-selectWarning-termYear--");
        }
        if (markCondition.getTermNum() != null&&!("".equals(markCondition.getTermNum()))) {
            sql.append(" and term_num=#{termNum}");
            System.out.println("SqlProvider-selectWarning-termNum--");
        }
        return sql.toString();
    }

    /**
     * @param markCondition
     * @Description 查询学生的总学分
     * @author 20155884 王晓阳
     * @methodName findStuCredithour
     * @returnType Msg
     **/
//COALESCE(SUM(c.course_credithour),0)
    public String findStuCredithour1(MarkCondition markCondition){
        StringBuffer sql = new StringBuffer("select m.*,SUM(c.course_credithour) AS stuCourseCresithour,c.*,t.*,tm.*,s.stu_name,s.stu_num " +
                "from mark m,course c,course_term t ,student s,term tm " +
                "where 1=1 " +
                "and m.course_id=c.course_id " +
                "and t.course_id=c.course_id " +
                "and m.mark>=60 " +
                "AND m.term_id=t.term_id " +
                "AND tm.term_id=t.term_id " +
                "and m.stu_info_id=s.stu_info_id ");
        //学生id
        if (markCondition.getStuInfoId()!= null) {
            sql.append(" and m.stu_info_id=#{stuInfoId}  ");
            System.out.println("--stu_info_id--");
        }
        //学期id
        if (markCondition.getTermId() != null) {
            sql.append(" and m.term_id=#{termId}");
            System.out.println("--termId--");
        }
        System.out.println("sql--"+sql.toString());
        return sql.toString();
    }
}
